﻿<%@ WebHandler Language="C#" Class="FeedbackTypeMaint" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Collections;
using ReportBusiness;

/*
 * Description: MES问题快反类型维护
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.4.20
 */

public class FeedbackTypeMaint : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    TreeObject t = new TreeObject();
    List<TreeObject> treeNodes = new List<TreeObject>();
    UserImage u = new UserImage();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getOrginazition":
                        r = getOrginazition(jObject);//查询BPM组织结构
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getUserInfo":
                        r = getUserInfo(jObject);//查询人员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCreateUser":
                        r = getCreateUser(jObject);//查询创建人
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "update":
                        r = update(jObject);//更新问题类型(单条数据)
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactory":
                        r = getFactory(jObject);//查询BPM分厂
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "addProblemType":
                        r = addProblemType(jObject);//新增
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "changeStatus":
                        r = changeStatus(jObject);//更新启用状态
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    case "changeUserBatch":
                        r = changeUserBatch(jObject);//批量更新人员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "checkSelectUserIsExists":
                        r = checkSelectUserIsExists(jObject);//检查所选人员是否存在
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "updateAll":
                        r = updateAll(jObject);//更新全部
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "changeOrgBatch":
                        r = changeOrgBatch(jObject);//批量修改组织
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSuperAdmin":
                        r = getSuperAdmin(jObject);//查询信息中心管理员
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询问题类型
    Result getData(JObject jObject)
    {
        string sql = @"
SELECT t.id,t.createdate,e.fullname createuser,e.employeename createUserName,mp.liuwei createUserLiuWei,
       t.updatedate,
       t.status,
       e.employeeid,
       t.problemtype,
       t.factoryname,
       nvl(wk.departmentname,t.workcenter) workcenter,
       nvl(st.departmentname,t.shiftname) shiftname,
       nvl(tm.departmentname,t.teamname) teamname,
       t.factoryname || DECODE(nvl(wk.departmentname,t.workcenter),NULL,'','/' || t.workcenter) 
                     || DECODE(nvl(st.departmentname,t.shiftname),NULL,'','/' || t.shiftname) 
                     || DECODE(nvl(tm.departmentname,t.teamname),NULL,'','/' || t.teamname) orginfo,
       t.lczg,lczg.employeename lczgUserName,lczg.liuwei lczgLiuWei, --流程主管
       t.cs,cs.employeename csUserName,cs.liuwei csLiuWei,--处室级
       t.fcj,fcj.employeename fcjUserName,fcj.liuwei fcjLiuWei,--副厂级
       t.cj,cj.employeename cjUserName,cj.liuwei cjLiuWei,--厂级
       t.scb,scb.employeename scbUserName,scb.liuwei scbLiuWei,--生产部
       t.fbz,fbz.employeename fbzUserName,fbz.liuwei fbzLiuWei,--副部长
       t.bz,bz.employeename bzUserName,bz.liuwei bzLiuWei,--部长
       t.fzjl,fzjl.employeename fzjlUserName,fzjl.liuwei fzjlLiuWei,--副总经理
       t.ld,ld.employeename ldUserName,ld.liuwei ldLiuWei --公司领导
FROM bo_eu_mes_problem_type t
INNER JOIN employee e ON 'MES_' || e.employeename = t.createuser
LEFT JOIN mes_userimage_mapping mp ON mp.employeename = e.employeename
LEFT JOIN mes_userimage_mapping lczg ON lczg.employeename = substr(t.lczg,5,8)
LEFT JOIN mes_userimage_mapping cs ON cs.employeename = substr(t.cs,5,8)
LEFT JOIN mes_userimage_mapping fcj ON fcj.employeename = substr(t.fcj,5,8)
LEFT JOIN mes_userimage_mapping cj ON cj.employeename = substr(t.cj,5,8)
LEFT JOIN mes_userimage_mapping scb ON scb.employeename = substr(t.scb,5,8)
LEFT JOIN mes_userimage_mapping fbz ON fbz.employeename = substr(t.fbz,5,8)
LEFT JOIN mes_userimage_mapping bz ON bz.employeename = substr(t.bz,5,8)
LEFT JOIN mes_userimage_mapping fzjl ON fzjl.employeename = substr(t.fzjl,5,8)
LEFT JOIN mes_userimage_mapping ld ON ld.employeename = substr(t.ld,5,8)
LEFT JOIN bpm_orgdepartment_table wk on wk.departmentid=t.workcenterid
LEFT JOIN bpm_orgdepartment_table tm on tm.departmentid=t.teamid
LEFT JOIN bpm_orgdepartment_table st on st.departmentid=t.shiftid
WHERE 1=1 ";

        //组织架构
        if (jObject["org"].ToString() != "[]")
        {
            string factoryname = "", workcentername = "", shiftname = "", teamname = "";
            JArray arr = JArray.Parse(jObject["org"].ToString());
            if (arr.Count > 0)
            {
                factoryname = arr[0]["label"].ToString();
                sql += string.Format(" AND t.factoryname = '{0}'", factoryname);
            }

            // 选择了分厂/工区 2级
            if (arr.Count > 1)
            {
                workcentername = arr[1]["label"].ToString();
                sql += string.Format(" AND t.workcenter = '{0}'", workcentername);
            }

            // 选择了分厂/工区/班/组 4级
            if (arr.Count == 4)
            {
                shiftname = arr[2]["label"].ToString();
                teamname = arr[3]["label"].ToString();
                sql += string.Format(" AND t.shiftname = '{0}' AND t.teamname = '{1}'", shiftname, teamname);
            }

            // 选择了3级，需要判断第3级是班还是组
            if (arr.Count == 3)
            {
                // 查询第3级组织结构具体是哪种类型
                string bpmOrgSql = @"SELECT t.departmentname,t.departmentid,t.departmenttype 
FROM bpm_orgdepartment_table t 
WHERE t.departmentid='{0}'";
                bpmOrgSql = string.Format(bpmOrgSql, arr[2]["value"].ToString());
                DataTable dtBpmOrg = dal.GetDataTable(bpmOrgSql);
                if (dtBpmOrg.Rows.Count > 0)
                {
                    if (dtBpmOrg.Rows[0]["departmenttype"].ToString() == "班")
                    {
                        shiftname = arr[2]["label"].ToString();
                        sql += string.Format(" AND t.shiftname = '{0}'", shiftname);
                    }
                    else if (dtBpmOrg.Rows[0]["departmenttype"].ToString() == "组")
                    {
                        teamname = arr[2]["label"].ToString();
                        sql += string.Format(" AND t.teamname = '{0}'", teamname);
                    }
                }
            }
        }

        if (jObject["createuser"].ToString() != "")
        {
            sql += string.Format(" AND t.createuser = '{0}'", jObject["createuser"].ToString().Split('<')[0]);
        }

        if (jObject["problemtype"].ToString() != "")
        {
            sql += string.Format(" AND t.problemtype like '%{0}%'", jObject["problemtype"].ToString());
        }

        if (jObject["bpmuser"].ToString() != "" && jObject["rolename"].ToString() != "")
        {
            string bpmuser = jObject["bpmuser"].ToString();
            string rolename = jObject["rolename"].ToString();
            if (rolename == "流程主管")
            {
                sql += string.Format(" AND t.lczg = '{0}'", bpmuser);
            }
            if (rolename == "处室")
            {
                sql += string.Format(" AND t.cs = '{0}'", bpmuser);
            }
            if (rolename == "副厂级")
            {
                sql += string.Format(" AND t.fcj = '{0}'", bpmuser);
            }
            if (rolename == "厂级")
            {
                sql += string.Format(" AND t.cj = '{0}'", bpmuser);
            }
            if (rolename == "副部长")
            {
                sql += string.Format(" AND t.fbz = '{0}'", bpmuser);
            }
            if (rolename == "部长")
            {
                sql += string.Format(" AND t.bz = '{0}'", bpmuser);
            }
            if (rolename == "生产部部长")
            {
                sql += string.Format(" AND t.fzjl = '{0}'", bpmuser);
            }
            if (rolename == "公司领导")
            {
                sql += string.Format(" AND t.ld = '{0}'", bpmuser);
            }
        }

        if (jObject["status"].ToString() != "")
        {
            sql += string.Format(" AND t.status = '{0}'", jObject["status"].ToString());
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);

        DataTable dt = dto.DBTable;

        dt.Columns.Add("createUserImages", typeof(string));//提出人
        dt.Columns.Add("lczgUserImages", typeof(string));//流程主管
        dt.Columns.Add("csUserImages", typeof(string));//处室处室人员
        dt.Columns.Add("fcjUserImages", typeof(string));//副厂级
        dt.Columns.Add("cjUserImages", typeof(string));//厂级
        dt.Columns.Add("scbUserImages", typeof(string));//生产部

        dt.Columns.Add("fbzUserImages", typeof(string));//副部长
        dt.Columns.Add("bzUserImages", typeof(string));//部长
        dt.Columns.Add("fzjlUserImages", typeof(string));//副总经理
        dt.Columns.Add("ldUserImages", typeof(string));//公司领导

        //获取提出人头像
        u.getUserImage(dt, "createUserImages", "createUserName", "createUserLiuWei");

        //获取流程主管头像
        u.getUserImage(dt, "lczgUserImages", "lczgUserName", "lczgLiuWei");

        //获取处室人员头像
        u.getUserImage(dt, "csUserImages", "csUserName", "csLiuWei");

        //获取副厂级头像
        u.getUserImage(dt, "fcjUserImages", "fcjUserName", "fcjLiuWei");

        //获取厂级头像
        u.getUserImage(dt, "cjUserImages", "cjUserName", "cjLiuWei");

        //获取生产部头像
        u.getUserImage(dt, "scbUserImages", "scbUserName", "scbLiuWei");

        //获取副部长头像
        u.getUserImage(dt, "fbzUserImages", "fbzUserName", "fbzLiuWei");

        //获取部长头像
        u.getUserImage(dt, "bzUserImages", "bzUserName", "bzLiuWei");

        //获取副总经理头像
        u.getUserImage(dt, "fzjlUserImages", "fzjlUserName", "fzjlLiuWei");

        //获取公司领导头像
        u.getUserImage(dt, "ldUserImages", "ldUserName", "ldLiuWei");
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询BPM组织结构
    Result getOrginazition(JObject jObject)
    {
        string sql = @"SELECT d.id,d.departmentname label,d.parentdepartmentid parentid FROM awsprd.feedback_orgdepartment@bpm195 d";
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        treeNodes = t.BindTree(dt, null, "0");
        r.data = treeNodes;
        r.total = Convert.ToInt32(0);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询创建人
    Result getCreateUser(JObject jObject)
    {
        string sql = @"
SELECT m.username,
       m.factoryname || DECODE(m.workcentername,NULL,'','/' || m.workcentername)
       || DECODE(m.teamname,NULL,'','/' || m.teamname) orginfo
FROM (
SELECT t.userid || '<' || t.username || '>' username,
       CASE
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '分厂' THEN
          t.dname
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '工区' THEN
          t.d2name
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '班' THEN
          t.d3name
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' THEN
          NVL(t.d4name, t.d3name)
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) IS NULL THEN
          NULL
       END factoryname,
       CASE
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '分厂' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '工区' THEN
          t.dname
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '班' THEN
          t.d2name
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' AND t.d4name IS NOT NULL THEN
          NVL(t.d3name, t.d2name)
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' AND t.d4name IS NULL THEN
          t.d2name
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) IS NULL THEN
          NULL
       END workcentername,
       CASE
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '分厂' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '工区' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '班' THEN
          t.dname
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' AND t.d4name IS NOT NULL THEN
          NVL(t.d2name, t.dname)
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' AND t.d4name IS NULL THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) IS NULL THEN
          NULL
       END shiftname,
       CASE
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '分厂' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '工区' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '班' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' THEN
          t.dname
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) IS NULL THEN
          NULL
       END teamname
FROM (SELECT DISTINCT u.id, u.userid, u.username, u.departmentid, d.departmenttype dtype, d2.departmenttype d2type, d3.departmenttype d3type,
              d4.departmenttype d4type, d.departmentname dname, d2.departmentname d2name, d3.departmentname d3name, d4.departmentname d4name
       FROM bo_eu_mes_problem_type t
       INNER JOIN awsprd.feedback_orguser@bpm195 u ON u.userid = t.createuser
       LEFT JOIN awsprd.feedback_orgdepartment@bpm195 d ON d.id = u.departmentid
       LEFT JOIN awsprd.feedback_orgdepartment@bpm195 d2 ON d2.id = d.parentdepartmentid
       LEFT JOIN awsprd.feedback_orgdepartment@bpm195 d3 ON d3.id = d2.parentdepartmentid
       LEFT JOIN awsprd.feedback_orgdepartment@bpm195 d4 ON d4.id = d3.parentdepartmentid
     ) t
) m";
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getUserInfo(JObject jObject)
    {
        string sql = @"
SELECT m.username,
       m.factoryname || DECODE(m.workcentername,NULL,'','/' || m.workcentername) || DECODE(m.teamname,NULL,'','/' || m.teamname) orginfo
FROM (
SELECT t.userid || '<' || t.username || '>' username,
       CASE
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '分厂' THEN
          t.dname
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '工区' THEN
          t.d2name
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '班' THEN
          t.d3name
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' THEN
          NVL(t.d4name, t.d3name)
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) IS NULL THEN
          NULL
       END factoryname,
       CASE
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '分厂' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '工区' THEN
          t.dname
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '班' THEN
          t.d2name
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' AND t.d4name IS NOT NULL THEN
          NVL(t.d3name, t.d2name)
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' AND t.d4name IS NULL THEN
          t.d2name
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) IS NULL THEN
          NULL
       END workcentername,
       CASE
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '分厂' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '工区' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '班' THEN
          t.dname
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' AND t.d4name IS NOT NULL THEN
          NVL(t.d2name, t.dname)
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' AND t.d4name IS NULL THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) IS NULL THEN
          NULL
       END shiftname,
       CASE
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '分厂' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '工区' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '班' THEN
          NULL
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) = '组' THEN
          t.dname
         WHEN NVL(NVL(NVL(t.dtype, t.d2type), t.d3type), t.d4type) IS NULL THEN
          NULL
       END teamname
FROM (SELECT DISTINCT u.id, u.userid, u.username, u.departmentid, d.departmenttype dtype, d2.departmenttype d2type, d3.departmenttype d3type,
              d4.departmenttype d4type, d.departmentname dname, d2.departmentname d2name, d3.departmentname d3name, d4.departmentname d4name
       FROM bpm_problem_role_users ru
       LEFT JOIN bpm_problem_role r ON r.roleid = ru.roleid
       INNER JOIN awsprd.feedback_orguser@bpm195 u ON u.userid = ru.username
       LEFT JOIN awsprd.feedback_orgdepartment@bpm195 d ON d.id = u.departmentid
       LEFT JOIN awsprd.feedback_orgdepartment@bpm195 d2 ON d2.id = d.parentdepartmentid
       LEFT JOIN awsprd.feedback_orgdepartment@bpm195 d3 ON d3.id = d2.parentdepartmentid
       LEFT JOIN awsprd.feedback_orgdepartment@bpm195 d4 ON d4.id = d3.parentdepartmentid
       WHERE r.rolename = '{0}'
     ) t
) m";
        DataSet ds = new DataSet();
        DataTable dtlczg = dal.GetDataTable(string.Format(sql, "流程主管"));
        dtlczg.TableName = "lczg";

        DataTable dtcs = dal.GetDataTable(string.Format(sql, "处室"));
        dtcs.TableName = "cs";

        DataTable dtfcj = dal.GetDataTable(string.Format(sql, "副厂级"));
        dtfcj.TableName = "fcj";

        DataTable dtcj = dal.GetDataTable(string.Format(sql, "厂级"));
        dtcj.TableName = "cj";


        DataTable dtfbz = dal.GetDataTable(string.Format(sql, "副部长"));
        dtfbz.TableName = "fbz";

        DataTable dtbz = dal.GetDataTable(string.Format(sql, "部长"));
        dtbz.TableName = "bz";

        DataTable dtfzjl = dal.GetDataTable(string.Format(sql, "生产部部长"));
        dtfzjl.TableName = "fzjl";

        DataTable dtld = dal.GetDataTable(string.Format(sql, "公司领导"));
        dtld.TableName = "ld";

        ds.Tables.Add(dtlczg);
        ds.Tables.Add(dtcs);
        ds.Tables.Add(dtfcj);
        ds.Tables.Add(dtcj);
        ds.Tables.Add(dtfbz);
        ds.Tables.Add(dtbz);
        ds.Tables.Add(dtfzjl);
        ds.Tables.Add(dtld);

        foreach (DataTable dt in ds.Tables)
        {
            foreach (DataColumn col in dt.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
        }
        Result r = new Result();
        r.data = ds;
        r.total = Convert.ToInt32(ds.Tables.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //更新问题类型(单条数据)
    Result update(JObject jObject)
    {
        string factoryname = "", workcentername = "", shiftname = "", teamname = "", workcenterid = "", shiftid = "", teamid = "";
        if (jObject["selectedOrg"] != null)
        {
            JArray arr = JArray.Parse(jObject["selectedOrg"].ToString());
            if (arr.Count > 0)
            {
                factoryname = arr[0]["label"].ToString();
            }

            // 选择了分厂/工区 2级
            if (arr.Count > 1)
            {
                workcentername = arr[1]["label"].ToString();
                workcenterid = arr[1]["value"].ToString();
            }

            // 选择了分厂/工区/班/组 4级
            if (arr.Count == 4)
            {
                shiftname = arr[2]["label"].ToString();
                teamname = arr[3]["label"].ToString();
                shiftid = arr[2]["value"].ToString();
                teamid = arr[3]["value"].ToString();
            }

            // 选择了3级，需要判断第3级是班还是组
            if (arr.Count == 3)
            {
                // 查询第3级组织结构具体是哪种类型
                string bpmSql = @"SELECT t.departmentname,t.id,t.departmenttype FROM awsprd.feedback_orgdepartment@bpm195 t WHERE t.id='{0}'";
                bpmSql = string.Format(bpmSql, arr[2]["value"].ToString());
                DataTable dt = dal.GetDataTable(bpmSql);
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["departmenttype"].ToString() == "班")
                    {
                        shiftname = arr[2]["label"].ToString();
                        shiftid = arr[2]["value"].ToString();
                    }
                    else if (dt.Rows[0]["departmenttype"].ToString() == "组")
                    {
                        teamname = arr[2]["label"].ToString();
                        teamid = arr[2]["value"].ToString();
                    }
                }
            }
        }

        string sql = @"
UPDATE bo_eu_mes_problem_type t 
SET t.problemtype='{0}',t.factoryname = '{1}',t.workcenter='{2}',t.shiftname='{3}',t.teamname='{4}',
    t.lczg='{5}',t.cs='{6}',t.fcj='{7}',t.cj='{8}',t.fbz='{9}',t.bz='{10}',t.fzjl='{11}',t.ld='{12}',
    t.updatedate = sysdate,workcenterid='{14}',teamid='{15}',shiftid='{16}'
WHERE t.id='{13}' 
";

        sql = string.Format(sql,
            jObject["problemtype"].ToString(),//问题类型
            factoryname,//分厂
            workcentername,//工区
            shiftname,//班
            teamname,//组
            jObject["lczg"].ToString(),//流程主管
            jObject["cs"].ToString(),//处室
            jObject["fcj"].ToString(),//副厂级
            jObject["cj"].ToString(),//厂级
            jObject["fbz"].ToString(),//副部长
            jObject["bz"].ToString(),//部长
            jObject["fzjl"].ToString(),//副总经理
            jObject["ld"].ToString(),//公司领导
            jObject["id"].ToString(), workcenterid, teamid, shiftid);

        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        if (count > 0)
        {
            r.data = null;
            r.total = Convert.ToInt32(1);
            r.meta = new Result.Meta(200, "更新成功");
        }
        else
        {
            r.data = null;
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(200, "更新失败");
        }
        return r;
    }

    //查询BPM分厂
    Result getFactory(JObject jObject)
    {
        string sql = @"SELECT dept.departmentname factoryname FROM awsprd.feedback_orgdepartment@bpm195 dept WHERE dept.departmenttype = '分厂'";
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    // 添加问题类型
    Result addProblemType(JObject jObject)
    {
        string factoryname = "", workcentername = "", shiftname = "", teamname = "", workcenterid = "", shiftid = "", teamid = "";
        if (jObject["selectedOrg"] != null)
        {
            JArray arr = JArray.Parse(jObject["selectedOrg"].ToString());
            if (arr.Count > 0)
            {
                factoryname = arr[0]["label"].ToString();
            }

            // 选择了分厂/工区 2级
            if (arr.Count > 1)
            {
                workcentername = arr[1]["label"].ToString();
                workcenterid = arr[1]["value"].ToString();
            }

            // 选择了分厂/工区/班/组 4级
            if (arr.Count == 4)
            {
                shiftname = arr[2]["label"].ToString();
                teamname = arr[3]["label"].ToString();
                shiftid = arr[2]["value"].ToString();
                teamid = arr[3]["value"].ToString();
            }

            // 选择了3级，需要判断第3级是班还是组
            if (arr.Count == 3)
            {
                // 查询第3级组织结构具体是哪种类型
                string bpmSql = @"SELECT t.departmentname,t.id,t.departmenttype FROM awsprd.feedback_orgdepartment@bpm195 t WHERE t.id='{0}'";
                bpmSql = string.Format(bpmSql, arr[2]["value"].ToString());
                DataTable dt = dal.GetDataTable(bpmSql);
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["departmenttype"].ToString() == "班")
                    {
                        shiftname = arr[2]["label"].ToString();
                        shiftid = arr[2]["value"].ToString();
                    }
                    else if (dt.Rows[0]["departmenttype"].ToString() == "组")
                    {
                        teamname = arr[2]["label"].ToString();
                        teamid = arr[2]["value"].ToString();
                    }
                }
            }
        }

        string sql = @" SELECT * FROM bo_eu_mes_problem_type t WHERE 1=1 ";
        sql += string.Format(" AND t.problemtype = '{0}'", jObject["problemtype"].ToString());
        if (factoryname != "")
        {
            sql += string.Format(" AND t.factoryname = '{0}'", factoryname);
        }
        if (workcentername != "")
        {
            sql += string.Format(" AND t.workcenter = '{0}'", workcentername);
        }
        if (shiftname != "")
        {
            sql += string.Format(" AND t.shiftname = '{0}'", shiftname);
        }
        if (teamname != "")
        {
            sql += string.Format(" AND t.teamname = '{0}'", teamname);
        }

        Result r = new Result();
        if (dal.GetDataTable(sql).Rows.Count != 0)
        {
            r.data = null;
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(200, "数据已存在，不能重复添加");
            return r;
        }

        sql = @"
INSERT INTO bo_eu_mes_problem_type t 
(id,orgid,bindid,createdate,createuser,updatedate,updateuser,
processdefid,isend,problemtype,factoryname,workcenter,teamname,
lczg,cs,fcj,cj,scb,shiftname,fbz,bz,fzjl,ld,status,workcenterid,teamid,shiftid
) 
VALUES(
'{0}','b3a6ea44-2259-4d97-a82f-b7267f5ec800',NULL,SYSDATE,'{1}',SYSDATE,'{2}',
'obj_266032352b3d4b58a15ca0eafb59c496','1','{3}','{4}','{5}','{6}',
'{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}',1,'{17}','{18}','{19}'
)";
        sql = string.Format(sql,
            Guid.NewGuid().ToString(),//唯一id
            "MES_" + jObject["employeename"].ToString(),//创建人createuser
            "MES_" + jObject["employeename"].ToString(),//更新人updateuser
            jObject["problemtype"].ToString(),//问题类型problemtype
            factoryname,//分厂factoryname
            workcentername,//工区workcenter
            teamname,//组teamname
            jObject["lczg"].ToString(),//流程主管lczg
            jObject["cs"].ToString(),//处室cs
            jObject["fcj"].ToString(),//副厂级fcj
            jObject["cj"].ToString(),//厂级cj
            "",//生产部scb
            shiftname,//班shiftname
            jObject["fbz"].ToString(),//副部长fbz
            jObject["bz"].ToString(),//部长bz
            jObject["fzjl"].ToString(),//副总经理fzjl
            jObject["ld"].ToString(),//公司领导ld
            workcenterid, teamid, shiftid
            );

        int count = dal.ExecuteSql(sql);

        if (count > 0)
        {
            r.data = null;
            r.total = Convert.ToInt32(1);
            r.meta = new Result.Meta(200, "添加成功");
        }
        else
        {
            r.data = null;
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(500, "添加失败");
        }
        return r;
    }

    //更新启用状态
    Result changeStatus(JObject jObject)
    {
        string sql = @" UPDATE bo_eu_mes_problem_type t SET t.status='{1}',t.updatedate = sysdate,t.updateuser='{2}' WHERE t.id='{0}' ";
        int status = 0;
        if (jObject["status"] != null)
        {
            status = jObject["status"].ToString() == "True" ? 1 : 0;
        }

        string employeename = "MES_" + jObject["employeename"].ToString();
        sql = string.Format(sql, jObject["id"].ToString(), status, employeename);
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    void exportExcel(JObject jObject)
    {
        string sql = @"
SELECT t.createdate,e.fullname createuser,DECODE(t.status,1,'已启用','未启用') status,t.problemtype,t.factoryname,t.workcenter,t.shiftname,t.teamname,t.lczg,t.cs,
       t.fcj,t.cj,t.fbz,t.bz,t.fzjl,t.ld
FROM bo_eu_mes_problem_type t
INNER JOIN employee e ON 'MES_' || e.employeename = t.createuser
WHERE 1=1 ";
        if (jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND t.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["createuser"].ToString() != "")
        {
            sql += string.Format(" AND t.createuser = '{0}'", jObject["createuser"].ToString().Split('<')[0]);
        }

        if (jObject["problemtype"].ToString() != "")
        {
            sql += string.Format(" AND t.problemtype like '%{0}%'", jObject["problemtype"].ToString());
        }

        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (dr["lczg"].ToString() != "" && dr["lczg"].ToString().Contains("<"))
            {
                dr["lczg"] = dr["lczg"].ToString().Split('<')[1].Replace(">", "");
            }
            if (dr["cs"].ToString() != "" && dr["cs"].ToString().Contains("<"))
            {
                dr["cs"] = dr["cs"].ToString().Split('<')[1].Replace(">", "");
            }
            if (dr["fcj"].ToString() != "" && dr["fcj"].ToString().Contains("<"))
            {
                dr["fcj"] = dr["fcj"].ToString().Split('<')[1].Replace(">", "");
            }
            if (dr["cj"].ToString() != "" && dr["cj"].ToString().Contains("<"))
            {
                dr["cj"] = dr["cj"].ToString().Split('<')[1].Replace(">", "");
            }
            if (dr["fbz"].ToString() != "" && dr["fbz"].ToString().Contains("<"))
            {
                dr["fbz"] = dr["fbz"].ToString().Split('<')[1].Replace(">", "");
            }
            if (dr["bz"].ToString() != "" && dr["bz"].ToString().Contains("<"))
            {
                dr["bz"] = dr["bz"].ToString().Split('<')[1].Replace(">", "");
            }
            if (dr["fzjl"].ToString() != "" && dr["fzjl"].ToString().Contains("<"))
            {
                dr["fzjl"] = dr["fzjl"].ToString().Split('<')[1].Replace(">", "");
            }
            if (dr["ld"].ToString() != "" && dr["ld"].ToString().Contains("<"))
            {
                dr["ld"] = dr["ld"].ToString().Split('<')[1].Replace(">", "");
            }
        }

        dtResult.Columns["createdate"].Caption = "创建时间";
        dtResult.Columns["createuser"].Caption = "创建人";
        dtResult.Columns["status"].Caption = "启用状态";
        dtResult.Columns["problemtype"].Caption = "问题类型";
        dtResult.Columns["factoryname"].Caption = "分厂";
        dtResult.Columns["workcenter"].Caption = "工区";
        dtResult.Columns["shiftname"].Caption = "班";
        dtResult.Columns["teamname"].Caption = "班组";
        dtResult.Columns["lczg"].Caption = "流程主管";
        dtResult.Columns["cs"].Caption = "处室";
        dtResult.Columns["fcj"].Caption = "副厂级";
        dtResult.Columns["cj"].Caption = "厂级";
        dtResult.Columns["fbz"].Caption = "副部长";
        dtResult.Columns["bz"].Caption = "部长";
        dtResult.Columns["fzjl"].Caption = "生产部审核";
        dtResult.Columns["ld"].Caption = "公司领导";
        ExportHelper.ExportExcel(dtResult, "MES问题快反类型清单");
    }

    //批量更新人员
    Result changeUserBatch(JObject jObject)
    {
        ArrayList sqlList = new ArrayList();
        string rolename = jObject["rolename"].ToString();//角色名称
        string field = "";
        if (rolename == "流程主管")
        {
            field = "lczg";
        }
        else if (rolename == "处室")
        {
            field = "cs";
        }
        else if (rolename == "副厂级")
        {
            field = "fcj";
        }
        else if (rolename == "厂级")
        {
            field = "cj";
        }
        else if (rolename == "副部长")
        {
            field = "fbz";
        }
        else if (rolename == "部长")
        {
            field = "bz";
        }
        else if (rolename == "生产部部长")
        {
            field = "fzjl";
        }
        else if (rolename == "公司领导")
        {
            field = "ld";
        }

        JArray selectData = JArray.Parse(jObject["selectData"].ToString());
        for (int i = 0; i < selectData.Count; i++)
        {
            sqlList.Add(string.Format("UPDATE bo_eu_mes_problem_type t SET t.updatedate = sysdate,t.{0} = '{1}' WHERE t.id = '{2}'",
                field, //修改的字段名
                jObject["bpmuser"].ToString(),//用户全名。格式为--> MES_用户id<用户姓名> 如：MES_69400364<寇延婷>
                selectData[i]["id"].ToString()));
        }
        Result r = new Result();
        try
        {
            dal.ExecuteSqlTran(sqlList);
            r.data = null;
            r.total = Convert.ToInt32(selectData.Count);
            r.meta = new Result.Meta(200, "添加成功");
            return r;
        }
        catch (Exception e)
        {
            r.data = null;
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(500, e.Message);
            return r;
        }
    }

    //查询所选用户是否存在
    Result checkSelectUserIsExists(JObject jObject)
    {
        string sql = @"
SELECT u.userid
FROM bpm_orguser_table u
WHERE u.userid || '<' || u.username || '>' = '{0}'";

        sql = string.Format(sql, jObject["bpmuser"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //更新全部
    Result updateAll(JObject jObject)
    {
        ArrayList sqlList = new ArrayList();
        string rolename = jObject["rolename"].ToString();//角色名称
        string field = "";
        if (rolename == "流程主管")
        {
            field = "lczg";
        }
        else if (rolename == "处室")
        {
            field = "cs";
        }
        else if (rolename == "副厂级")
        {
            field = "fcj";
        }
        else if (rolename == "厂级")
        {
            field = "cj";
        }
        else if (rolename == "副部长")
        {
            field = "fbz";
        }
        else if (rolename == "部长")
        {
            field = "bz";
        }
        else if (rolename == "生产部部长")
        {
            field = "fzjl";
        }
        else if (rolename == "公司领导")
        {
            field = "ld";
        }

        string sql = string.Format("UPDATE bo_eu_mes_problem_type t SET t.updatedate = sysdate,t.{0} = '{1}' WHERE 1=1", field, jObject["bpmuser"].ToString());

        int result = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(result);
        r.meta = new Result.Meta(200, "添加成功");
        return r;
    }

    //批量修改组织
    Result changeOrgBatch(JObject jObject)
    {
        ArrayList sqlList = new ArrayList();
        string factoryname = "", workcentername = "", shiftname = "", teamname = "";
        JArray arr = JArray.Parse(jObject["selectedOrg"].ToString());
        if (arr.Count > 0)
        {
            factoryname = arr[0]["label"].ToString();
        }

        // 选择了分厂/工区 2级
        if (arr.Count > 1)
        {
            workcentername = arr[1]["label"].ToString();
        }

        // 选择了分厂/工区/班/组 4级
        if (arr.Count == 4)
        {
            shiftname = arr[2]["label"].ToString();
            teamname = arr[3]["label"].ToString();
        }

        // 选择了3级，需要判断第3级是班还是组
        if (arr.Count == 3)
        {
            // 查询第3级组织结构具体是哪种类型
            DataTable dt = dal.GetDataTable(string.Format("SELECT t.departmentname,t.id,t.departmenttype FROM awsprd.feedback_orgdepartment@bpm195 t WHERE t.id='{0}'", arr[2]["value"].ToString()));
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["departmenttype"].ToString() == "班")
                {
                    shiftname = arr[2]["label"].ToString();
                }
                else if (dt.Rows[0]["departmenttype"].ToString() == "组")
                {
                    teamname = arr[2]["label"].ToString();
                }
            }
        }

        JArray selectData = JArray.Parse(jObject["selectData"].ToString());
        for (int i = 0; i < selectData.Count; i++)
        {
            string sql = @"
UPDATE bo_eu_mes_problem_type t SET t.factoryname = '{1}',t.workcenter='{2}',t.shiftname='{3}',t.teamname='{4}',t.updatedate = sysdate 
WHERE t.id='{0}'";
            sql = string.Format(sql, selectData[i]["id"].ToString(), factoryname, workcentername, shiftname, teamname);
            sqlList.Add(sql);
        }

        Result r = new Result();
        try
        {
            dal.ExecuteSqlTran(sqlList);
            r.data = null;
            r.total = Convert.ToInt32(selectData.Count);
            r.meta = new Result.Meta(200, "更新成功");
            return r;
        }
        catch (Exception e)
        {
            r.data = null;
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(500, e.Message);
            return r;
        }
    }

    //查询信息中心管理员
    Result getSuperAdmin(JObject jObject)
    {
        string sql = "SELECT e.employeeid FROM employee e WHERE e.employeename IN ('69400364','59100621', '50102249')";
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}